﻿using System;
using System.Data;

namespace AssettDatabase.DAL
{
    class Supplier
    {
        private string sqltext = "";

        public bool Add(string Supplier_Name, string Supplier_Phone, string Sup_Primary_Contact_FN, string Sup_Primary_Contact_LN, string Sup_Primary_Contact_EML,
                        string Supplier_Website, string Sup_Web_Username, string Sup_Web_Password, string Supplier_Customer_Code,
                        string Sup_Comments)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "INSERT INTO Suppliers (Supplier_Name,Supplier_Phone,Sup_Primary_Contact_FN,Sup_Primary_Contact_LN,Sup_Primary_Contact_EML,Supplier_Website,Sup_Web_Username," +
                            "Sup_Web_Password,Supplier_Customer_Code,Sup_Comments,Supplier_Status)" +
                            "VALUES (@Supplier_Name,@Supplier_Phone,@Sup_Primary_Contact_FN,@Sup_Primary_Contact_LN,@Sup_Primary_Contact_EML,@Supplier_Website,@Sup_Web_Username," +
                            "@Sup_Web_Password,@Supplier_Customer_Code,@Sup_Comments,1);";

                mDAO.AddParameter("@Supplier_Name", Supplier_Name);
                mDAO.AddParameter("@Supplier_Phone", Supplier_Phone);
                mDAO.AddParameter("@Sup_Primary_Contact_FN", Sup_Primary_Contact_FN);
                mDAO.AddParameter("@Sup_Primary_Contact_LN", Sup_Primary_Contact_LN);
                mDAO.AddParameter("@Sup_Primary_Contact_EML", Sup_Primary_Contact_EML);
                mDAO.AddParameter("@Supplier_Website", Supplier_Website);
                mDAO.AddParameter("@Sup_Web_Username", Sup_Web_Username);
                mDAO.AddParameter("@Sup_Web_Password", Sup_Web_Password);
                mDAO.AddParameter("@Supplier_Customer_Code", Supplier_Customer_Code);
                mDAO.AddParameter("@Sup_Comments", Sup_Comments);
                
                mDAO.ExecuteNonQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return true;
        }


        public bool Update(string Supplier_Name, string Supplier_Phone, string Sup_Primary_Contact_FN, string Sup_Primary_Contact_LN, string Sup_Primary_Contact_EML,
                        string Supplier_Website, string Sup_Web_Username, string Sup_Web_Password, string Supplier_Customer_Code,
                        string Sup_Comments, int Supplier_ID)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "UPDATE Suppliers SET Supplier_Name=@Supplier_Name," +
                            "Supplier_Phone=@Supplier_Phone," +
                            "Sup_Primary_Contact_FN=@Sup_Primary_Contact_FN," +
                            "Sup_Primary_Contact_LN=@Sup_Primary_Contact_LN," +
                            "Sup_Primary_Contact_EML=@Sup_Primary_Contact_EML," +
                            "Supplier_Website=@Supplier_Website," +
                            "Sup_Web_Username=@Sup_Web_Username," +
                            "Sup_Web_Password=@Sup_Web_Password," +
                            "Supplier_Customer_Code=@Supplier_Customer_Code," +
                            "Sup_Comments=@Sup_Comments " +
                            "WHERE Supplier_ID=@Supplier_ID";

                mDAO.AddParameter("@Supplier_Name", Supplier_Name);
                mDAO.AddParameter("@Supplier_Phone", Supplier_Phone);
                mDAO.AddParameter("@Sup_Primary_Contact_FN", Sup_Primary_Contact_FN);
                mDAO.AddParameter("@Sup_Primary_Contact_LN", Sup_Primary_Contact_LN);
                mDAO.AddParameter("@Sup_Primary_Contact_EML", Sup_Primary_Contact_EML);
                mDAO.AddParameter("@Supplier_Website", Supplier_Website);
                mDAO.AddParameter("@Sup_Web_Username", Sup_Web_Username);
                mDAO.AddParameter("@Sup_Web_Password", Sup_Web_Password);
                mDAO.AddParameter("@Supplier_Customer_Code", Supplier_Customer_Code);
                mDAO.AddParameter("@Sup_Comments", Sup_Comments);
                mDAO.AddParameter("@Supplier_ID", Supplier_ID);

                mDAO.ExecuteNonQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return true;
        }

        public bool Del(int Supplier_ID)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "UPDATE Suppliers " +
                            "SET Supplier_Status=0 " +
                            "WHERE Supplier_ID=@Supplier_ID";

                mDAO.AddParameter("@Supplier_ID", Supplier_ID);
                mDAO.ExecuteNonQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return true;
        }
        public DataTable GetSupplier()
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "SELECT Supplier_Phone,Sup_Primary_Contact_FN,Sup_Primary_Contact_LN,Sup_Primary_Contact_EML,Supplier_Website,Sup_Web_Username," +
                            "Sup_Web_Password,Supplier_Customer_Code,Sup_Comments,Supplier_ID, " +
                            "CASE WHEN Supplier_Status = 1 THEN Supplier_Name ELSE CONCAT(Supplier_Name, ' (retired)') END AS Supplier_Name " +
                            "FROM Suppliers " +
                            "ORDER BY Supplier_Name";

                return mDAO.ExecuteSelectQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetSupplierById(int Supplier_ID)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "SELECT Supplier_Name, Supplier_Phone, Sup_Primary_Contact_FN, Sup_Primary_Contact_LN, Sup_Primary_Contact_EML, Supplier_Website, " +
                            "Sup_Web_Username, Sup_Web_Password, Supplier_Customer_Code, Supplier_ID, Sup_Comments " +
                            "From Suppliers " +
                            "WHERE Supplier_ID=@Supplier_ID";

                mDAO.AddParameter("@Supplier_ID", Supplier_ID);
                return mDAO.ExecuteSelectQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetSupplierByName(string Supplier_Name)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "SELECT Supplier_Name, Supplier_Phone, Sup_Primary_Contact_FN, Sup_Primary_Contact_LN, Sup_Primary_Contact_EML, Supplier_Website, " +
                            "Sup_Web_Username, Sup_Web_Password, Supplier_Customer_Code, Supplier_ID, Sup_Comments " +
                            "From Suppliers " +
                            "WHERE Supplier_Name=@Supplier_Name";

                mDAO.AddParameter("@Supplier_Name", Supplier_Name);
                return mDAO.ExecuteSelectQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
